|
|
In this post we will see how to create a CRUD in VB.Net and Microsoft Access. The full form of CRUD is create, read,update and delete records from the database. By the end of this tutorial you will be able to connect to the Microsoft Access database and insert, update, delete and read database from the database using VB.Net.
To create a VB.Net project about CRUD in VB.Net and Microsoft Access, first of all you have to design an interface of the form with six labels, five text boxes and six buttons on this form as shown in the image.
Next change the Text property of the labels as Id, Name, Class, Address, Contact and lblMessage and the name property of the textboxes to txtId, txtName, txtClas, txtAddress and txtContact.
Next, change the name property of the buttons as btnInsert, btnUpdate, btnDelete, btnSearch, btnClear and btnExit respectively.
If you want to learn about CRUD operations in VB.Net and Stored Procedures then click CRUD in VB.Net using Stored Procedures and if you want to learn about SQL injection attack in VB.Net and SQL Server just simply click SQL Injection attack in VB.Net and SQL Server.
Before you start typing the VB.Net code on the different buttons which you placed on the form of CRUD in VB.Net and Microsoft Access project first of all you have to create a database in the Microsoft Access with the name of students and also you need to create a table with the name of students with fields as shown below.
Now type the VB.Net code on different buttons of the form as under:
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Dim con As OleDbConnection
Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
clear()
End Sub
Sub clear()
txtID.Clear()
txtName.Clear()
txtClass.Clear()
txtAddress.Clear()
txtContact.Clear()
lblMessage.Text = ""
txtID.Focus()
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
End
End Sub
Public Sub RunQuery(ByVal query As String)
'con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\BMEO\Documents\Visual Studio 2019\Projects\CRUD-VB-Access\CRUD-VB-Access\bin\Debug\students.accdb")
'con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Application.StartupPath & \students.accdb")
con = New OleDbConnection("provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\BMEO\Documents\students.accdb")
Dim cmd As New OleDbCommand(query, con)
cmd.Parameters.AddWithValue("@name", txtName.Text)
cmd.Parameters.AddWithValue("@stclass", txtClass.Text)
cmd.Parameters.AddWithValue("@address", txtAddress.Text)
cmd.Parameters.AddWithValue("@contact", txtContact.Text)
cmd.Parameters.AddWithValue("@id", txtID.Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Sub
Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
Dim insertQuery As String = "insert into students (st_Name,st_Class,st_Address,st_Contact) VALUES(@name,@stclass,@address,@contact)"
RunQuery(insertQuery)
lblMessage.Text = "Record inserted successfully"
End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
Dim deleteQuery As String = "delete from students where ID=@id"
RunQuery(deleteQuery)
lblMessage.Text = "Record Deleted successfully"
End Sub
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Dim updatequery As String = "update students set st_Name=@name,st_Class=@stclass,st_Address=@address,st_Contact=@contact where ID=@id"
RunQuery(updatequery)
lblMessage.Text = "Record Updated successfully"
End Sub
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
con = New OleDbConnection("provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\BMEO\Documents\students.accdb")
Dim searchQuery As String = "select * from students where ID=@id"
Dim cmd As New OleDbCommand(searchQuery, con)
cmd.Parameters.AddWithValue("@id", txtID.Text)
Dim da As New OleDbDataAdapter(cmd)
Dim table As New DataTable
da.Fill(table)
If table.Rows.Count > 0 Then
txtName.Text = table.Rows(0)(1).ToString
txtClass.Text = table.Rows(0)(2).ToString
txtAddress.Text = table.Rows(0)(3).ToString
txtContact.Text = table.Rows(0)(4).ToString
lblMessage.Text = "Record found"
Else
clear()
lblMessage.Text = "Record not found"
End If
End Sub
End Class
Now, finally press F5 and run CRUD in VB.Net and Microsoft Access project.
If you want to watch the video tutorial of this post then please visit my YouTube channel sarfarazbhat and watch different programming videos or you can watch this tutorial right here.
If you want to watch the video tutorial of this post then please visit my YouTube channel sarfarazbhat and watch different programming videos or you can watch this tutorial right here.
0 comments:
Post a Comment